﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Web;
using DTO;
using Moved;



namespace BUS
{
    public class NhanVienBUS
    {
        private static String _connString = "";

        static NhanVienBUS()
        {
            _connString = ConfigurationManager.ConnectionStrings["WebChamCongConnectionString"].ConnectionString.ToString();
        }
        public static bool ChangeDepartment(int UserID, int DepartmentNew)
        {
            bool kt = false;
            using (Moved_DB dc = new Moved_DB(_connString))
            {
                var userinfo = (from us in dc.AL_UserInfo
                                where us.UserID == UserID
                                select us
                                  ).SingleOrDefault();
                if (userinfo != null)
                {
                    userinfo.DepartmentID = DepartmentNew;
                    dc.SubmitChanges();
                    kt = true;
                }

            }
            return kt;

        }

        public static DTO.NhanVienDTO searchInfoUserByID(string Acc)
        {
            String AccLocal = Acc;
            DTO.NhanVienDTO nvs = null;
            using (Moved.Moved_DB dc = new Moved.Moved_DB(_connString))
            {
                var nv = (from us in dc.AL_UserInfo
                          where string.Compare(us.ACNo, AccLocal) == 0
                          select us).Distinct();
                foreach (var n in nv)
                {
                    nvs = new NhanVienDTO();
                    nvs.UserId = n.UserID;
                    nvs.AcNo = n.ACNo;
                    nvs.Name = n.Name;
                    nvs.FullName = n.FullName;

                }

                return nvs;
            }
        }


        public static DTO.NhanVienDTO getUserIDByName(string Name)
        {
            String NameLocal = Name;
            DTO.NhanVienDTO nvs = null;
            using (Moved.Moved_DB dc = new Moved_DB(_connString))
            {
                var nv = (from us in dc.AL_UserInfo
                          join dp in dc.AL_Department on us.DepartmentID equals dp.DepartmentID
                          where string.Compare(us.Name, NameLocal) == 0
                          select new
                          {
                              id = us.UserID,
                              bpid = us.DepartmentID,
                              tenbp = dp.DepartmentName
                          }
                          ).Distinct();
                foreach (var n in nv)
                {
                    nvs = new NhanVienDTO();
                    nvs.UserId = n.id;
                    nvs.DepartmentId = (int)n.bpid;
                    nvs.DepartmentName = n.tenbp;
                }
                return nvs;
            }

        }

        public static List<DTO.NhanVienDTO> GetMsDeparment(int deparID)
        {
            List<DTO.NhanVienDTO> kq1 = new List<NhanVienDTO>();
            using (Moved.Moved_DB dc = new Moved_DB(_connString))
            {
                var objName = (from us in dc.AL_UserInfo
                               where us.DepartmentID == deparID
                               select new
                               {
                                   fullname = us.FullName,
                                   UserID = us.UserID
                               }).Distinct();
                foreach (var obj in objName)
                {
                    DTO.NhanVienDTO kq = new NhanVienDTO();
                    kq.FullName = obj.fullname;
                    kq.UserId = obj.UserID;
                    kq1.Add(kq);
                }
                return kq1;
            }
        }

        public static int CountNhanVienByDepartID(int departID)
        {
            int localDepartId = departID;
            int result = 0;

            using (Moved_DB dc = new Moved_DB(_connString))
            {
                var nvs = (from nv in dc.AL_UserInfo
                           where nv.DepartmentID == localDepartId
                           select nv
                               );
                result = nvs.Count();
            }

            return result;
        }
        public static bool UpdateNhanVien(int UserID, string name, string fullname, DateTime datebirth, string idnumber, DateTime date, string address1, string address2, string fone, string cellfone, string Email, int DepartmentID, string study)
        {
            bool kt = false;
            try
            {
                using (Moved.Moved_DB dc = new Moved_DB(_connString))
                {
                    var ListAcc = (from us in dc.AL_UserInfo
                                   where us.UserID == UserID
                                   select us
                                     ).SingleOrDefault();
                    if (ListAcc != null)
                    {
                        ListAcc.Name = name;
                        ListAcc.FullName = fullname;
                        ListAcc.Brithday = datebirth;
                        ListAcc.CMND = idnumber;
                        ListAcc.DateCMND = date;
                        ListAcc.PermanentResidence = address1;
                        ListAcc.TemporaryResidence = address2;
                        ListAcc.Phone = fone;
                        ListAcc.Telephone = cellfone;
                        ListAcc.Email = Email;
                        ListAcc.DepartmentID = DepartmentID;
                        ListAcc.Study = study;

                        dc.SubmitChanges();
                        kt = true;

                    }

                }
            }
            catch (Exception e)
            {
                kt = false;
                throw e;
            }

            return kt;
        }
        public static DTO.NhanVienDTO GetNhanViensByID(int userId)
        {
            int localUserId = userId;
            DTO.NhanVienDTO objNhanVien = null;

            using (Moved_DB dc = new Moved_DB(_connString))
            {
                var nhanVien = (from nv in dc.AL_UserInfo
                                join dp in dc.AL_Department on nv.DepartmentID equals dp.DepartmentID
                                where nv.UserID == localUserId
                                select new
                                {
                                    id = nv.UserID,
                                    acNo = nv.ACNo,
                                    cardNo = nv.CardNo,
                                    name = nv.Name,
                                    privilege = nv.Privilege,
                                    fullName = nv.FullName,
                                    birthday = nv.Brithday,
                                    sex = nv.Sex,
                                    permantResident = nv.PermanentResidence,
                                    temporaryResident = nv.TemporaryResidence,
                                    cmnd = nv.CMND,
                                    dateCMND = nv.DateCMND,
                                    addressCMND = nv.AdressCMND,
                                    phone = nv.Phone,
                                    telephone = nv.Telephone,
                                    email = nv.Email,
                                    martialID = nv.MaritalID,
                                    religion = nv.Religion,
                                    country = nv.Country,
                                    study = nv.Study,
                                    language = nv.Language,
                                    hiredDay = nv.Hiredday,
                                    departID = nv.DepartmentID,
                                    positionID = nv.PositionID,
                                    statusWork = nv.StatusWorkID,
                                    attendance = nv.IsAttendance,
                                    departName = dp.DepartmentName
                                }
                                       );

                foreach (var item in nhanVien)
                {
                    objNhanVien = new NhanVienDTO();
                    objNhanVien.UserId = item.id;
                    objNhanVien.AcNo = item.acNo;
                    objNhanVien.CardNo = item.cardNo;
                    objNhanVien.Name = item.name;
                    objNhanVien.FullName = item.fullName;
                    objNhanVien.PermantResidance = item.permantResident;
                    objNhanVien.TemporaryResidance = item.temporaryResident;
                    objNhanVien.CMND = item.cmnd;
                    objNhanVien.AddressCmnd = item.addressCMND;
                    objNhanVien.Phone = item.phone;
                    objNhanVien.Telephone = item.telephone;
                    objNhanVien.Email = item.email;
                    objNhanVien.Religion = item.religion;
                    objNhanVien.Country = item.country;
                    objNhanVien.Study = item.study;
                    objNhanVien.Language = item.language;
                    objNhanVien.DepartmentId = (int)item.departID;
                    objNhanVien.IsAttendance = (bool)item.attendance;
                    objNhanVien.DepartmentName = item.departName;
                }


            }

            return objNhanVien;
        }

        public static List<DTO.NhanVienDTO> GetNhanViensByDepartID(Int32 departId)
        {
            Int32 localDepartID = departId;

            List<DTO.NhanVienDTO> listNhanViens = new List<NhanVienDTO>();

            using (Moved.Moved_DB dc = new Moved_DB(_connString))
            {
                var nhanViens = (from nv in dc.AL_UserInfo
                                 where nv.DepartmentID == localDepartID
                                 select new
                                 {
                                     id = nv.UserID,
                                     acNo = nv.ACNo,
                                     cardNo = nv.CardNo,
                                     name = nv.Name,
                                     privilege = nv.Privilege,
                                     fullName = nv.FullName,
                                     birthday = nv.Brithday,
                                     sex = nv.Sex,
                                     permantResident = nv.PermanentResidence,
                                     temporaryResident = nv.TemporaryResidence,
                                     cmnd = nv.CMND,
                                     dateCMND = nv.DateCMND,
                                     addressCMND = nv.AdressCMND,
                                     phone = nv.Phone,
                                     telephone = nv.Telephone,
                                     email = nv.Email,
                                     martialID = nv.MaritalID,
                                     religion = nv.Religion,
                                     country = nv.Country,
                                     study = nv.Study,
                                     language = nv.Language,
                                     hiredDay = nv.Hiredday,
                                     departID = nv.DepartmentID,
                                     positionID = nv.PositionID,
                                     statusWork = nv.StatusWorkID,
                                     attendance = nv.IsAttendance
                                 }
                                         ).Distinct().OrderBy(temp => temp.name);

                foreach (var item in nhanViens)
                {
                    NhanVienDTO nv = new NhanVienDTO();
                    nv.UserId = item.id;
                    nv.AcNo = item.acNo;
                    nv.CardNo = item.cardNo;
                    nv.Name = item.name;
                    nv.FullName = item.fullName;
                    //nv.Birthday = (DateTime) item.birthday;
                    //nv.Sex = (int) item.sex;
                    nv.PermantResidance = item.permantResident;
                    nv.TemporaryResidance = item.temporaryResident;
                    nv.CMND = item.cmnd;
                    //nv.DateCmnd = (DateTime) item.dateCMND;
                    nv.AddressCmnd = item.addressCMND;
                    nv.Phone = item.phone;
                    nv.Telephone = item.telephone;
                    nv.Email = item.email;
                    //nv.MartialId = (int) item.martialID;
                    nv.Religion = item.religion;
                    nv.Country = item.country;
                    nv.Study = item.study;
                    nv.Language = item.language;
                    //nv.HiredDay = (DateTime) item.hiredDay;
                    nv.DepartmentId = (int)item.departID;
                    //nv.PositionId = (int) item.positionID;
                    //nv.StatusWorkId = (int) item.statusWork;
                    nv.IsAttendance = (bool)item.attendance;

                    listNhanViens.Add(nv);
                }

            }

            return listNhanViens;
        }
        public static List<DTO.NhanVienDTO> GetNhanViensByFullName()
        {

            List<DTO.NhanVienDTO> listNhanViens = new List<NhanVienDTO>();

            using (Moved.Moved_DB dc = new Moved_DB(_connString))
            {
                var nhanViens = (from nv in dc.AL_UserInfo
                                 where nv.FullName == null
                                 select new
                                 {
                                     id = nv.UserID,
                                     acNo = nv.ACNo,
                                     cardNo = nv.CardNo,
                                     name = nv.Name,
                                     privilege = nv.Privilege,
                                     fullName = nv.FullName,
                                     birthday = nv.Brithday,
                                     sex = nv.Sex,
                                     permantResident = nv.PermanentResidence,
                                     temporaryResident = nv.TemporaryResidence,
                                     cmnd = nv.CMND,
                                     dateCMND = nv.DateCMND,
                                     addressCMND = nv.AdressCMND,
                                     phone = nv.Phone,
                                     telephone = nv.Telephone,
                                     email = nv.Email,
                                     martialID = nv.MaritalID,
                                     religion = nv.Religion,
                                     country = nv.Country,
                                     study = nv.Study,
                                     language = nv.Language,
                                     hiredDay = nv.Hiredday,
                                     departID = nv.DepartmentID,
                                     positionID = nv.PositionID,
                                     statusWork = nv.StatusWorkID,
                                     attendance = nv.IsAttendance
                                 }
                                         ).Distinct().OrderBy(temp => temp.name);

                foreach (var item in nhanViens)
                {
                    NhanVienDTO nv = new NhanVienDTO();
                    nv.UserId = item.id;
                    nv.AcNo = item.acNo;
                    nv.CardNo = item.cardNo;
                    nv.Name = item.name;
                    nv.FullName = item.fullName;
                    if (Convert.ToString(item.birthday) == "" || Convert.ToString(item.birthday) == "" || Convert.ToString(item.hiredDay) == "" || item.sex == null || item.martialID == null || item.positionID == null || item.statusWork == null)
                    {
                        nv.Birthday = new DateTime(2000, 1, 1, 11, 0, 0);
                        nv.HiredDay = new DateTime(2000, 1, 1, 11, 0, 0);
                        nv.DateCmnd = new DateTime(2000, 1, 1, 11, 0, 0);
                        nv.Sex = 0;
                        nv.PositionId = 0;
                        nv.MartialId = 0;
                        nv.StatusWorkId = 0;
                    }
                    else
                    {
                        nv.MartialId = (int)item.martialID;
                        nv.DateCmnd = (DateTime)item.dateCMND;
                        nv.Birthday = (DateTime)item.birthday;
                        nv.HiredDay = (DateTime)item.hiredDay;
                        nv.PositionId = (int)item.positionID;
                        nv.StatusWorkId = (int)item.statusWork;
                        nv.Sex = (int)item.sex;
                    }


                    nv.PermantResidance = item.permantResident;
                    nv.TemporaryResidance = item.temporaryResident;
                    nv.CMND = item.cmnd;
                    // 
                    nv.AddressCmnd = item.addressCMND;
                    nv.Phone = item.phone;
                    nv.Telephone = item.telephone;
                    nv.Email = item.email;

                    nv.Religion = item.religion;
                    nv.Country = item.country;
                    nv.Study = item.study;
                    nv.Language = item.language;
                    // 
                    nv.DepartmentId = (int)item.departID;


                    nv.IsAttendance = (bool)item.attendance;

                    listNhanViens.Add(nv);
                }

            }

            return listNhanViens;
        }
        public static DTO.NhanVienDTO GetNhanViensByACNO(int ACNO)
        {
            int localAcNo = ACNO;
            DTO.NhanVienDTO role = null;

            using (Moved.Moved_DB dc = new Moved_DB(_connString))
            {
                var objRole = (from nv in dc.AL_UserInfo
                               where nv.UserID == localAcNo
                               select new
                               {
                                   id = nv.UserID,
                                   acNo = nv.ACNo,
                                   cardNo = nv.CardNo,
                                   name = nv.Name,
                                   privilege = nv.Privilege,
                                   fullName = nv.FullName,
                                   birthday = nv.Brithday,
                                   sex = nv.Sex,
                                   permantResident = nv.PermanentResidence,
                                   temporaryResident = nv.TemporaryResidence,
                                   cmnd = nv.CMND,
                                   dateCMND = nv.DateCMND,
                                   addressCMND = nv.AdressCMND,
                                   phone = nv.Phone,
                                   telephone = nv.Telephone,
                                   email = nv.Email,
                                   martialID = nv.MaritalID,
                                   religion = nv.Religion,
                                   country = nv.Country,
                                   study = nv.Study,
                                   language = nv.Language,
                                   hiredDay = nv.Hiredday,
                                   departID = nv.DepartmentID,
                                   positionID = nv.PositionID,
                                   statusWork = nv.StatusWorkID,
                                   attendance = nv.IsAttendance
                               });
                foreach (var kq in objRole)
                {
                    role = new NhanVienDTO();
                    role.UserId = kq.id;
                    role.AcNo = kq.acNo;
                    role.CardNo = kq.cardNo;
                    role.Name = kq.name;
                    role.FullName = kq.fullName;
                    if (Convert.ToString(kq.birthday) == "" || Convert.ToString(kq.birthday) == "" || Convert.ToString(kq.hiredDay) == "" || kq.sex == null || kq.martialID == null || kq.positionID == null || kq.statusWork == null)
                    {
                        role.Birthday = new DateTime(2000, 1, 1, 11, 0, 0);
                        role.HiredDay = new DateTime(2000, 1, 1, 11, 0, 0);
                        role.DateCmnd = new DateTime(2000, 1, 1, 11, 0, 0);
                        role.Sex = 0;
                        role.PositionId = 0;
                        role.MartialId = 0;
                        role.StatusWorkId = 0;
                    }
                    else
                    {
                        role.MartialId = (int)kq.martialID;
                        role.DateCmnd = (DateTime)kq.dateCMND;
                        role.Birthday = (DateTime)kq.birthday;
                        role.HiredDay = (DateTime)kq.hiredDay;
                        role.PositionId = (int)kq.positionID;
                        role.StatusWorkId = (int)kq.statusWork;
                        role.Sex = (int)kq.sex;
                    }
                    role.PermantResidance = kq.permantResident;
                    role.TemporaryResidance = kq.temporaryResident;
                    role.CMND = kq.cmnd;
                    // 
                    role.AddressCmnd = kq.addressCMND;
                    role.Phone = kq.phone;
                    role.Telephone = kq.telephone;
                    role.Email = kq.email;

                    role.Religion = kq.religion;
                    role.Country = kq.country;
                    role.Study = kq.study;
                    role.Language = kq.language;
                    // 
                    role.DepartmentId = (int)kq.departID;


                    role.IsAttendance = (bool)kq.attendance;
                }
                return role;

            }


        }

        //add exceptional time into Hday columns
        public static void InsExtTime(int UserID, int Day, string time1, string time2)
        {
            int localID = UserID;
            int localDay = Day;
            string localtime1 = time1;
            string localtime2 = time2;
            using (Moved_DB dc = new Moved_DB(_connString))
            {
                var user = (from us in dc.WIT_Schedule
                            where us.UserID == localID
                            select us );
                foreach (var gu in user)
                {                    
                    switch (localDay)
                    {
                        case 1: gu.HDay1 = localtime1 + " " + localtime2; break;
                        case 2: gu.HDay2 = localtime1 + " " + localtime2; break;
                        case 3: gu.HDay3 = localtime1 + " " + localtime2; break;
                        case 4: gu.HDay4 = localtime1 + " " + localtime2; break;
                        case 5: gu.HDay5 = localtime1 + " " + localtime2; break;
                        case 6: gu.HDay6 = localtime1 + " " + localtime2; break;
                        case 7: gu.HDay7 = localtime1 + " " + localtime2; break;
                        case 8: gu.HDay8 = localtime1 + " " + localtime2; break;
                        case 9: gu.HDay9 = localtime1 + " " + localtime2; break;
                        case 10: gu.HDay10 = localtime1 + " " + localtime2; break;
                        case 11: gu.HDay11 = localtime1 + " " + localtime2; break;
                        case 12: gu.HDay12 = localtime1 + " " + localtime2; break;
                        case 13: gu.HDay13 = localtime1 + " " + localtime2; break;
                        case 14: gu.HDay14 = localtime1 + " " + localtime2; break;
                        case 15: gu.HDay15 = localtime1 + " " + localtime2; break;
                        case 16: gu.HDay16 = localtime1 + " " + localtime2; break;
                        case 17: gu.HDay17 = localtime1 + " " + localtime2; break;
                        case 18: gu.HDay18 = localtime1 + " " + localtime2; break;
                        case 19: gu.HDay19 = localtime1 + " " + localtime2; break;
                        case 20: gu.HDay20 = localtime1 + " " + localtime2; break;
                        case 21: gu.HDay21 = localtime1 + " " + localtime2; break;
                        case 22: gu.HDay22 = localtime1 + " " + localtime2; break;
                        case 23: gu.HDay23 = localtime1 + " " + localtime2; break;
                        case 24: gu.HDay24 = localtime1 + " " + localtime2; break;
                        case 25: gu.HDay25 = localtime1 + " " + localtime2; break;
                        case 26: gu.HDay26 = localtime1 + " " + localtime2; break;
                        case 27: gu.HDay27 = localtime1 + " " + localtime2; break;
                        case 28: gu.HDay28 = localtime1 + " " + localtime2; break;
                        case 29: gu.HDay29 = localtime1 + " " + localtime2; break;
                        case 30: gu.HDay30 = localtime1 + " " + localtime2; break;
                        case 31: gu.HDay31 = localtime1 + " " + localtime2; break;
                    }
                    dc.SubmitChanges();

                }

            }

        }
    }
}